package edu.zut.test;

import edu.zut.main.pojo.SysUser;
import edu.zut.main.util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * JDBC：增删改的模拟
 */
public class JDBCUpdate {

    public static void main(String[] args) throws SQLException {
//        SysUser sysUser = new SysUser();
//        sysUser.setUser_code("m_005");
//        sysUser.setUser_name("jack");
//        sysUser.setUser_password("456");
//        sysUser.setUser_state(1);
//        insert(sysUser);
//        queryList();
        delete(5);
    }

    /**
     * 查询多条数据
     */
    public static void queryList() throws SQLException {
        String sql = "select user_id id, user_code code, user_name name, user_password psw from sys_user";
        ResultSet rs = DBUtils.query(sql, null);
        List<SysUser> list = new ArrayList<>();
        //此时结果可能有多条
        while (rs.next()){
            //解析获取的每一行具体字段数据
            int user_id = rs.getInt("id");
            String user_code = rs.getString("code");
            String user_name = rs.getString("name");
            String user_password = rs.getString("psw");
            SysUser sysUser = new SysUser(user_id, user_code, user_name, user_password);
            list.add(sysUser);
        }
        System.out.println("list = " + list);
        DBUtils.free();
    }

    //？ select count(*)  from sys_user;


    /**
     * 模拟添加数据
     * @param sysUser 添加的数据
     * @throws SQLException
     */
    public static void insert(SysUser sysUser) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into  sys_user values (null,?,?,?,?)";
        //预处理sql
        PreparedStatement statement = conn.prepareStatement(sql);
        //sql中占位符的填充
        statement.setString(1,sysUser.getUser_code());
        statement.setString(2,sysUser.getUser_name());
        statement.setString(3,sysUser.getUser_password());
        statement.setInt(4,sysUser.getUser_state());
        //执行sql   executeUpdate : 增删改   返回int数据  代表被影响的行数
        int i = statement.executeUpdate();
        System.out.println("i = " + i);
        //资源的关闭
        DBUtils.free();
    }

    /**
     * 根据id删除
     */
    public static void delete(int id){
        String sql = "delete from sys_user where user_id = ?";
        int i = DBUtils.update(sql, id);
        System.out.println("i = " + i);
    }


    /**
     * 根据id修改用户编号
     */

}
